package com.gem.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Date;
import java.util.Map;

public class ExcelUtil {

    public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textList,
                                              int firstRow, int endRow, int firstCol, int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
        return sheet;
    }

    //标题样式（第一行和第二行）
    public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = getCenterStyle(workbook);
        //字体加粗对象
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        font.setBold(true);
        font.setFontHeightInPoints((short) 25);
        //给cellStyle单元格样式对象添加字体加粗
        cellStyle.setFont(font);
        return cellStyle;
    }

    //th表头的样式（各个字段）
    public static HSSFCellStyle getThStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle2 = getCenterStyle(workbook);
        //字体加粗对象
        Font font2 = workbook.createFont();
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 10);
        //给cellStyle单元格样式对象添加字体加粗
        cellStyle2.setFont(font2);
        return cellStyle2;
    }

    //模板的例子！
    public static HSSFCellStyle getExamplStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle3 = getCenterStyle(workbook);
        //字体加粗对象
        Font font3 = workbook.createFont();
        font3.setColor(Font.COLOR_RED);
        cellStyle3.setFont(font3);
        return cellStyle3;
    }

    // 单元格类型日期格式化
    public static HSSFCellStyle getDateStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyleDate = getCenterStyle(workbook);
        HSSFCreationHelper creationHelper = workbook.getCreationHelper();
        cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd"));
        return cellStyleDate;
    }

    // 单元格类型日期格式化(模板红色)
    public static HSSFCellStyle getDateRedStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyleDate = getCenterStyle(workbook);
        HSSFCreationHelper creationHelper = workbook.getCreationHelper();
        cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd"));
        Font font3 = workbook.createFont();
        font3.setColor(Font.COLOR_RED);
        cellStyleDate.setFont(font3);
        return cellStyleDate;
    }

    //垂直水平居中样式（表格主内容）
    public static HSSFCellStyle getCenterStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return cellStyle3;
    }

    //设置表头(Apolar_sh)
    public static HSSFRow setExcelHander(HSSFRow headRow, String[] handerArr) {
        int index = 0;
        for (String hander : handerArr) {
            headRow.createCell(index++).setCellValue(hander);
        }
        return headRow;
    }

    //设置导出数据(Apolar_sh)
    public static HSSFRow setExcelExample(HSSFRow row, Map<String, Object> map) {
        int index = 0;
        for (String key : map.keySet()) {
            if (key.contains("String")) {
                row.createCell(index).setCellValue((String) map.get(key));
            } else if (key.contains("Integer")) {
                row.createCell(index, CellType.NUMERIC).setCellValue((Integer) map.get(key));
            } else if (key.contains("Double")) {
                row.createCell(index, CellType.NUMERIC).setCellValue((Double) map.get(key));
            } else if (key.contains("Date")) {
                row.createCell(index).setCellValue((Date) map.get(key));
            }
            index++;
        }
        return row;
    }

    //读取导入数据(Apolar_sh)
    public static Object[] getExcelData(HSSFRow row, String[] type) {

        Object[] data = new Object[type.length];
        for (int i = 0; i < type.length; i++) {
            if ("String".equals(type[i])) {
                data[i] = row.getCell(i).getStringCellValue();
            } else if ("Double".equals(type[i])) {
                data[i] = row.getCell(i).getNumericCellValue();
            } else if ("Integer".equals(type[i])) {
                data[i] = (int) Math.ceil(row.getCell(i).getNumericCellValue());
            } else if ("Date".equals(type[i])) {
                data[i] = row.getCell(i).getDateCellValue();
            }
            System.err.println("i:"+i+",type:"+type[i]);
        }
        return data;
    }
}
